<?php
/**
 *
 * @author liubao
 * @version 1.0
 */

namespace App\Db\mysql;

use PDO;
use Doctrine\DBAL\Driver\PDOException;
use Closure;

class Mysql
{
    public $pdo = null;

    protected $select = '*';
    protected $from = null;
    protected $where = null;
    protected $limit = null;
    protected $offset = null;
    protected $join = null;
    protected $orderBy = null;
    protected $groupBy = null;
    protected $having = null;
    protected $grouped = false;
    protected $numRows = 0;
    protected $insertId = null;
    protected $query = null;
    protected $error = null;
    protected $result = [];
    protected $prefix = null;
    protected $op = ['=', "!=", '<', '>', "<=", ">=", "<>"];
    protected $cache = null;
    protected $cacheDir = null;
    protected $cacheTime = null;
    protected $queryCount = 0;
    protected $debug = true;
    protected $clearCache = true;

    // 当前数据库操作对象
    protected $db = null;
    // 数据库对象池
    private $_db = array();

    static private $instance = array();     //  数据库连接实例

    public function __construct(Array $config = [], $linkNum = 0)
    {
        $this->prefix = (isset($config["prefix"]) ? $config["prefix"] : '');
        $this->debug = (isset($config["debug"]) ? $config["debug"] : false);

        return $this->db($linkNum, empty($this->connection) ? $config : $this->connection, true);
    }

    public function table($table)
    {
        if (is_array($table)) {
            $f = '';
            foreach ($table as $key)
                $f .= $this->prefix . $key . ", ";

            $this->from = rtrim($f, ", ");
        } else
            $this->from = $this->prefix . $table;

        return $this;
    }

    public function select($fields)
    {
        $select = (is_array($fields) ? implode(", ", $fields) : $fields);
        $this->select = ($this->select == '*' ? $select : $this->select . ", " . $select);

        return $this;
    }

    public function max($field, $name = null)
    {
        $func = "MAX(" . $field . ')' . (!is_null($name) ? " AS " . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ", " . $func);

        return $this;
    }

    public function min($field, $name = null)
    {
        $func = "MIN(" . $field . ')' . (!is_null($name) ? " AS " . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ", " . $func);

        return $this;
    }

    public function sum($field, $name = null)
    {
        $func = "SUM(" . $field . ')' . (!is_null($name) ? " AS " . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ", " . $func);

        return $this;
    }

    public function count($field, $name = null)
    {
        $func = "COUNT(" . $field . ')' . (!is_null($name) ? " AS " . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ", " . $func);

        return $this;
    }

    public function avg($field, $name = null)
    {
        $func = "AVG(" . $field . ')' . (!is_null($name) ? " AS " . $name : '');
        $this->select = ($this->select == '*' ? $func : $this->select . ", " . $func);

        return $this;
    }

    public function join($table, $field1 = null, $op = null, $field2 = null, $type = '')
    {
        $on = $field1;
        $table = $this->prefix . $table;

        if (!is_null($op))
            $on = (!in_array($op, $this->op) ? $this->prefix . $field1 . " = " . $this->prefix . $op : $this->prefix . $field1 . ' ' . $op . ' ' . $this->prefix . $field2);

        if (is_null($this->join))
            $this->join = ' ' . $type . "JOIN" . ' ' . $table . " ON " . $on;
        else
            $this->join = $this->join . ' ' . $type . "JOIN" . ' ' . $table . " ON " . $on;

        return $this;
    }

    public function innerJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, "INNER ");

        return $this;
    }

    public function leftJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, "LEFT ");

        return $this;
    }

    public function rightJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, "RIGHT ");

        return $this;
    }

    public function fullOuterJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, "FULL OUTER ");

        return $this;
    }

    public function leftOuterJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, "LEFT OUTER ");

        return $this;
    }

    public function rightOuterJoin($table, $field1, $op = '', $field2 = '')
    {
        $this->join($table, $field1, $op, $field2, "RIGHT OUTER ");

        return $this;
    }

    public function where($where, $op = null, $val = null, $type = '', $and_or = "AND")
    {
        if (is_array($where)) {
            $_where = [];

            foreach ($where as $column => $data)
                $_where[] = $type . $column . '=' . $this->escape($data);

            $where = implode(' ' . $and_or . ' ', $_where);
        } else {
            if (is_array($op)) {
                $x = explode('?', $where);
                $w = '';

                foreach ($x as $k => $v)
                    if (!empty($v))
                        $w .= $type . $v . (isset($op[$k]) ? $this->escape($op[$k]) : '');

                $where = $w;
            } elseif (!in_array($op, $this->op) || $op == false)
                $where = $type . $where . " = " . $this->escape($op);
            else
                $where = $type . $where . ' ' . $op . ' ' . $this->escape($val);
        }

        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }

        if (is_null($this->where))
            $this->where = $where;
        else
            $this->where = $this->where . ' ' . $and_or . ' ' . $where;

        return $this;
    }

    public function orWhere($where, $op = null, $val = null)
    {
        $this->where($where, $op, $val, '', "OR");

        return $this;
    }

    public function notWhere($where, $op = null, $val = null)
    {
        $this->where($where, $op, $val, "NOT ", "AND");

        return $this;
    }

    public function orNotWhere($where, $op = null, $val = null)
    {
        $this->where($where, $op, $val, "NOT ", "OR");

        return $this;
    }

    public function grouped(Closure $obj)
    {
        $this->grouped = true;
        call_user_func_array($obj, [$this]);
        $this->where .= ')';

        return $this;
    }

    public function in($field, Array $keys, $type = '', $and_or = "AND")
    {
        if (is_array($keys)) {
            $_keys = [];

            foreach ($keys as $k => $v)
                $_keys[] = (is_numeric($v) ? $v : $this->escape($v));

            $keys = implode(", ", $_keys);


            $where = $field . ' ' . $type . "IN (" . $keys . ')';

            if ($this->grouped) {
                $where = '(' . $where;
                $this->grouped = false;
            }

            if (is_null($this->where))
                $this->where = $where;
            else
                $this->where = $this->where . ' ' . $and_or . ' ' . $where;
        }

        return $this;
    }

    public function notIn($field, Array $keys)
    {
        $this->in($field, $keys, "NOT ", "AND");

        return $this;
    }

    public function orIn($field, Array $keys)
    {
        $this->in($field, $keys, '', "OR");

        return $this;
    }

    public function orNotIn($field, Array $keys)
    {
        $this->in($field, $keys, "NOT ", "OR");

        return $this;
    }

    public function between($field, $value1, $value2, $type = '', $and_or = "AND")
    {

        $where = $field . ' ' . $type . "BETWEEN " . $this->escape($value1) . " AND " . $this->escape($value2);

        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }

        if (is_null($this->where))
            $this->where = $where;
        else
            $this->where = $this->where . ' ' . $and_or . ' ' . $where;

        return $this;
    }

    public function notBetween($field, $value1, $value2)
    {
        $this->between($field, $value1, $value2, "NOT ", "AND");

        return $this;
    }

    public function orBetween($field, $value1, $value2)
    {
        $this->between($field, $value1, $value2, '', "OR");

        return $this;
    }

    public function orNotBetween($field, $value1, $value2)
    {
        $this->between($field, $value1, $value2, "NOT ", "OR");

        return $this;
    }

    public function like($field, $data, $type = '', $and_or = "AND")
    {
        $like = $this->escape($data);

        $where = $field . ' ' . $type . "LIKE " . $like;

        if ($this->grouped) {
            $where = '(' . $where;
            $this->grouped = false;
        }

        if (is_null($this->where))
            $this->where = $where;
        else
            $this->where = $this->where . ' ' . $and_or . ' ' . $where;

        return $this;
    }

    public function orLike($field, $data)
    {
        $this->like($field, $data, '', "OR");

        return $this;
    }

    public function notLike($field, $data)
    {
        $this->like($field, $data, "NOT ", "AND");

        return $this;
    }

    public function orNotLike($field, $data)
    {
        $this->like($field, $data, "NOT ", "OR");

        return $this;
    }

    public function limit($limit, $limitEnd = null)
    {
        if (!is_null($limitEnd))
            $this->limit = $limit . ", " . $limitEnd;
        else
            $this->limit = $limit;

        return $this;
    }

    public function offset($offset)
    {
        $this->offset = $offset;

        return $this;
    }

    public function pagination($perPage, $page)
    {
        $this->limit = $perPage;
        $this->offset = ($page - 1) * $perPage;

        return $this;
    }

    public function orderBy($orderBy, $order_dir = null)
    {
        if (!is_null($order_dir))
            $this->orderBy = $orderBy . ' ' . strtoupper($order_dir);
        else {
            if (stristr($orderBy, ' ') || $orderBy == "rand()")
                $this->orderBy = $orderBy;
            else
                $this->orderBy = $orderBy . " ASC";
        }

        return $this;
    }

    public function groupBy($groupBy)
    {
        if (is_array($groupBy))
            $this->groupBy = implode(", ", $groupBy);
        else
            $this->groupBy = $groupBy;

        return $this;
    }

    public function having($field, $op = null, $val = null)
    {
        if (is_array($op)) {
            $x = explode('?', $field);
            $w = '';

            foreach ($x as $k => $v)
                if (!empty($v))
                    $w .= $v . (isset($op[$k]) ? $this->escape($op[$k]) : '');

            $this->having = $w;
        } elseif (!in_array($op, $this->op))
            $this->having = $field . " > " . $this->escape($op);
        else
            $this->having = $field . ' ' . $op . ' ' . $this->escape($val);

        return $this;
    }

    public function numRows()
    {
        return $this->numRows;
    }

    public function insertId()
    {
        return $this->insertId;
    }

    public function error()
    {
        if ($this->debug) {
            $msg = '<h1>Database Error</h1>';
            $msg .= '<h4>Query: <em style="font-weight:normal;">"' . $this->query . '"</em></h4>';
            $msg .= '<h4>Error: <em style="font-weight:normal;">' . $this->error . '</em></h4>';
            die($msg);
        } else
            return false;
    }

    public function get($type = false)
    {
        $this->limit = 1;
        $query = $this->getAll(true);

        if ($type === true)
            return $query;
        else
            return $this->query($query, false, (($type == "array") ? true : false));
    }

    public function getAll($type = false)
    {
        $query = "SELECT " . $this->select . " FROM " . $this->from;

        if (!is_null($this->join))
            $query .= $this->join;

        if (!is_null($this->where))
            $query .= " WHERE " . $this->where;

        if (!is_null($this->groupBy))
            $query .= " GROUP BY " . $this->groupBy;

        if (!is_null($this->having))
            $query .= " HAVING " . $this->having;

        if (!is_null($this->orderBy))
            $query .= " ORDER BY " . $this->orderBy;

        if (!is_null($this->limit))
            $query .= " LIMIT " . $this->limit;

        if (!is_null($this->offset))
            $query .= " OFFSET " . $this->offset;

        if ($type === true)
            return $query;
        else
            return $this->query($query, true, (($type == "array") ? true : false));
    }

    public function insert($data)
    {
        $columns = array_keys($data);
        $column = implode(',', $columns);
        $val = implode(", ", array_map([$this, "escape"], $data));

        $query = "INSERT INTO " . $this->from . " (" . $column . ") VALUES (" . $val . ')';
        $query = $this->query($query);

        if ($query) {
            $this->insertId = $this->db->lastInsertId();

            return $this->insertId();
        } else
            return false;
    }

    public function update($data)
    {
        $query = "UPDATE " . $this->from . " SET ";
        $values = [];

        foreach ($data as $column => $val)
            $values[] = $column . '=' . $this->escape($val);

        $query .= (is_array($data) ? implode(',', $values) : $data);

        if (!is_null($this->where))
            $query .= " WHERE " . $this->where;

        if (!is_null($this->orderBy))
            $query .= " ORDER BY " . $this->orderBy;

        if (!is_null($this->limit))
            $query .= " LIMIT " . $this->limit;

        return $this->query($query);
    }

    public function delete()
    {
        $query = "DELETE FROM " . $this->from;

        if (!is_null($this->where))
            $query .= " WHERE " . $this->where;

        if (!is_null($this->orderBy))
            $query .= " ORDER BY " . $this->orderBy;

        if (!is_null($this->limit))
            $query .= " LIMIT " . $this->limit;

        if ($query == "DELETE FROM " . $this->from)
            $query = "TRUNCATE TABLE " . $this->from;

        return $this->query($query);
    }

    public function analyze()
    {
        return $this->query("ANALYZE TABLE " . $this->from);
    }

    public function check()
    {
        return $this->query("CHECK TABLE " . $this->from);
    }

    public function checksum()
    {
        return $this->query("CHECKSUM TABLE " . $this->from);
    }

    public function optimize()
    {
        return $this->query("OPTIMIZE TABLE " . $this->from);
    }

    public function repair()
    {
        return $this->query("REPAIR TABLE " . $this->from);
    }

    public function query($query, $all = true, $array = false)
    {
        $this->reset();

        if (is_array($all)) {
            $x = explode('?', $query);
            $q = '';

            foreach ($x as $k => $v)
                if (!empty($v))
                    $q .= $v . (isset($all[$k]) ? $this->escape($all[$k]) : '');

            $query = $q;
        }

        $this->query = preg_replace("/\s\s+|\t\t+/", ' ', trim($query));

        $str = false;
        foreach (["select", "optimize", "check", "repair", "checksum", "analyze"] as $value) {
            if (stripos($this->query, $value) === 0) {
                $str = true;
                break;
            }
        }

        $cache = false;
        if (!is_null($this->cache)) {
            $cache = $this->cache->get('php_' . $this->query);
            $cache = $cache ? json_decode($cache) : [];
        }

        if (!$cache && $str) {
            $sql = $this->db->query($this->query);

            if ($sql) {
                $this->numRows = $sql->rowCount();

                if (($this->numRows > 0)) {
                    if ($all) {
                        $q = [];

                        while ($result = ($array == false) ? $sql->fetchAll(PDO::FETCH_OBJ) : $sql->fetchAll(PDO::FETCH_ASSOC))
                            $q[] = $result;

                        $this->result = $q[0];
                    } else {
                        $q = ($array == false) ? $sql->fetch(PDO::FETCH_OBJ) : $sql->fetch(PDO::FETCH_ASSOC);
                        $this->result = $q;
                    }
                }

                if (!is_null($this->cache))
                    $this->cache->setex('php_' . $this->query, $this->cacheTime, json_encode($this->result));

                $this->cache = null;
            } else {
                $this->cache = null;
                $this->error = $this->db->errorInfo();
                $this->error = $this->error[2];

                return $this->error();
            }
        } elseif ((!$cache && !$str) || ($cache && !$str)) {
            $this->cache = null;
            $this->result = $this->db->exec($this->query);

            if ($this->result === false) {
                $this->error = $this->db->errorInfo();
                $this->error = $this->error[2];

                return $this->error();
            }
        } else {
            $this->cache = null;
            $this->result = $cache;
        }

        $this->queryCount++;

        return $this->result;
    }

    public function escape($data)
    {
        if ($data === NULL)
            return 'NULL';

        if (is_null($data))
            return null;

        return $this->db->quote(trim($data));
    }

    public function cache($time)
    {
        $this->cacheTime = $time;
        $this->cache = Cache::redis();

        return $this;
    }

    public function clearCache($cacheDir)
    {
        $this->cacheDir = $cacheDir;
        $FileCache = new FileCache();
        $this->clearCache = $FileCache->delete($cacheDir);
        return $this;
    }

    public function queryCount()
    {
        return $this->queryCount;
    }

    public function getQuery()
    {
        return $this->query;
    }

    protected function reset()
    {
        $this->select = '*';
        //$this->from = null;
        $this->where = null;
        $this->limit = null;
        $this->offset = null;
        $this->orderBy = null;
        $this->groupBy = null;
        $this->having = null;
        $this->join = null;
        $this->grouped = false;
        $this->numRows = 0;
        $this->insertId = null;
        $this->query = null;
        $this->error = null;
        $this->result = [];

        return;
    }

    function __destruct()
    {
        $this->db = null;
    }

    /**
     * 切换当前的数据库连接
     * @access public
     * @param integer $linkNum 连接序号
     * @param mixed $config 数据库连接信息
     * @param boolean $force 强制重新连接
     * @return Model
     */
    public function db($linkNum = '', $config = '', $force = false)
    {
        if ('' === $linkNum && $this->db) {
            return $this->db;
        }

        if (!isset($this->_db[$linkNum]) || $force) {
            // 创建一个新的实例
            $this->_db[$linkNum] = self::getInstance($config);
        } elseif (NULL === $config) {
            $this->_db[$linkNum]->close(); // 关闭数据库连接
            unset($this->_db[$linkNum]);
            return;
        }

        // 切换数据库连接
        $this->db = $this->_db[$linkNum];

        return $this;
    }

    /**
     * 取得数据库类实例
     * @static
     * @access public
     * @param mixed $config 连接配置
     * @return Object 返回数据库驱动类
     */
    public static function getInstance($config = array())
    {
        $md5 = md5(serialize($config));
        if (!isset(self::$instance[$md5])) {
            // 解析连接参数 支持数组和字符串
            $dsn = '';
            if ($config["driver"] == "mysql" || $config["driver"] == '' || $config["driver"] == "pgsql")
                $dsn = $config["driver"] . ":host=" . $config["host"] . ';'
                    . (($config["port"]) != '' ? "port=" . $config["port"] . ';' : '')
                    . "dbname=" . $config["database"];

            elseif ($config["driver"] == "sqlite")
                $dsn = "sqlite:" . $config["database"];

            elseif ($config["driver"] == "oracle")
                $dsn = "oci:dbname=" . $config["host"] . '/' . $config["database"];
            try {
                self::$instance[$md5] = new PDO($dsn, $config["username"], $config["password"]);
                self::$instance[$md5]->exec("SET NAMES '" . $config["charset"] . "' COLLATE '" . $config["collation"] . "'");
                self::$instance[$md5]->exec("SET CHARACTER SET '" . $config["charset"] . "'");
                self::$instance[$md5]->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);
            } catch (PDOException $e) {
                die("Cannot the connect to Database with PDO.<br /><br />" . $e->getMessage());
            }
        }
        return self::$instance[$md5];
    }
}
